package com.authine.cloudpivot.ext.controller.workList;

import cn.hutool.core.lang.Assert;
import com.authine.cloudpivot.engine.api.model.organization.UserModel;
import com.authine.cloudpivot.engine.api.model.runtime.BizObjectCreatedModel;
import com.authine.cloudpivot.engine.api.model.runtime.WorkItemModel;
import com.authine.cloudpivot.engine.api.model.runtime.WorkflowInstanceModel;
import com.authine.cloudpivot.engine.enums.status.SequenceStatus;
import com.authine.cloudpivot.ext.Utils.CustomSchemaCode;
import com.authine.cloudpivot.ext.service.CloudSqlService;
import com.authine.cloudpivot.web.api.controller.base.BaseController;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;

/**
 * 常态工作清单
 **/
@RestController
@RequestMapping("/public/normalWorkList")
@Slf4j
public class NormalWorkListController extends BaseController {


    @Autowired
    CloudSqlService sqlService;

    /**
     * 常态工作清单  审批流完成后 数据写到-常态工作清单 基础
     */
    @RequestMapping("finish")
    public void finish(String bizId) {
        BizObjectCreatedModel bizObject = getBizObjectFacade().getBizObject(CustomSchemaCode.NORMAL_WORK_LIST, bizId);

        List<Map<String, Object>> list = (List<Map<String, Object>>) bizObject.get(CustomSchemaCode.NORMAL_WORK_LIST_DETAIL);

        String now = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));

        //获取审批人
        String approval = "";
//        String approval = getFileBaseInfoWorkFlowApproval(bizObject);

        for (Map<String, Object> map : list) {

            try {
                //获取数据
                Map<String, Object> data = fileInfoBaseMap(map, now, approval);
                //判断是否存在

                String id = existsBizObject01(map);
                if (StringUtils.isNotEmpty(id)) {
                    data.put("id", id);
                }

                BizObjectCreatedModel model = new BizObjectCreatedModel(CustomSchemaCode.NORMAL_WORK_LIST_BASE, data, false);
                model.setSequenceStatus(SequenceStatus.COMPLETED.name());
                id = getBizObjectFacade().saveBizObject(CustomSchemaCode.adminUserId, model, false);
                log.info("基础表-常态工作清单操作成功 ");
                //调用存储过程
                String fid = callProcess(id);

//                String NORMAL_WORK_LIST_BASETableName = getBizObjectFacade().getTableName(CustomSchemaCode.NORMAL_WORK_LIST_BASE);
//                StringBuffer updateFidSql = new StringBuffer("update ").append(NORMAL_WORK_LIST_BASETableName).append(" set fid='")
//                        .append(fid).append("' where id='").append(id).append("';");
//                sqlService.update(updateFidSql.toString());

            } catch (Exception e) {
                log.info("基础表-常态工作清单案操作失败 sheetId={},parentId={}", map.get("id"), bizId);
                log.info(e.getMessage(), e);
            }

        }
    }

    /**
     * 常态工作清单 修改  审批流完成后 数据写到-常态工作清单 基础
     */
    @RequestMapping("update")
    public void update(String bizId) {
        BizObjectCreatedModel bizObject = getBizObjectFacade().getBizObject(CustomSchemaCode.normalWorkListUpdate, bizId);
        List<Map<String, Object>> list = (List<Map<String, Object>>) bizObject.get(CustomSchemaCode.normalWorkListDetailUpdate);

        String now = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
        //获取审批人
        String approval = getFileBaseInfoWorkFlowApproval(bizObject);
        for (Map<String, Object> map : list) {
            try {
                //获取数据
                Map<String, Object> data = fileInfoBaseMap(map, now, approval);
                //判断是否已存在，如果存在，更新
                String normalWorkListDetailBaseId = existsBizObject(map);
                if (StringUtils.isNotBlank(normalWorkListDetailBaseId)) {
                    data.put("id", normalWorkListDetailBaseId);
                }

                BizObjectCreatedModel model = new BizObjectCreatedModel(CustomSchemaCode.NORMAL_WORK_LIST_BASE, data, false);
                model.setSequenceStatus(SequenceStatus.COMPLETED.name());
                String id = getBizObjectFacade().saveBizObject(CustomSchemaCode.adminUserId, model, false);
                log.info("基础表-常态工作清单操作成功 ");
                //调用存储过程
                 callProcess(id);
//                String NORMAL_WORK_LIST_BASETableName = getBizObjectFacade().getTableName(CustomSchemaCode.NORMAL_WORK_LIST_BASE);
//                StringBuffer updateFidSql = new StringBuffer("update ").append(NORMAL_WORK_LIST_BASETableName).append(" set fid='")
//                        .append(fid).append("' where id='").append(id).append("';");
//                sqlService.update(updateFidSql.toString());
            } catch (Exception e) {
                log.info("基础表-常态工作清单案操作失败 sheetId={},parentId={}", map.get("id"), bizId);
                log.info(e.getMessage(), e);
            }
        }
    }

    @RequestMapping("toVoid")
    public void toVoid(String bizId) {
        BizObjectCreatedModel bizObject = getBizObjectFacade().getBizObject(CustomSchemaCode.VOID_NORMAL_WORK_LIST, bizId);
        List<Map<String, Object>> list = (List<Map<String, Object>>) bizObject.get(CustomSchemaCode.VOID_NORMAL_WORK_LIST_DETAIL);

        String now = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));

        //获取审批人

        String approval = getFileBaseInfoWorkFlowApproval(bizObject);

        for (Map<String, Object> map : list) {


            try {
                //获取数据
                Map<String, Object> data = fileInfoBaseMap(map, now, approval);
                //判断是否已存在，如果存在，更新
                String normalWorkListDetailBaseId = existsBizObject(map);
                //判断是否已存在，如果不存在，放弃这条数据，直接跳过此次循环
                if (StringUtils.isBlank(normalWorkListDetailBaseId)) {
                    continue;
                }
                data.put("id", normalWorkListDetailBaseId);

                BizObjectCreatedModel model = new BizObjectCreatedModel(CustomSchemaCode.NORMAL_WORK_LIST_BASE, data, false);
                model.setSequenceStatus(SequenceStatus.CANCELED.name());
                String id = getBizObjectFacade().saveBizObject(CustomSchemaCode.adminUserId, model, false);
                log.info("基础表-常态工作清单作废操作成功 ");
                //调用存储过程
                callProcessToVoid(id);

            } catch (Exception e) {
                log.info("基础表-常态工作清单案操作失败 sheetId={},parentId={}", map.get("id"), bizId);
                log.info(e.getMessage(), e);
            }

        }
    }


    /**
     * -------常态工作清单
     * exec   SyncNormalWork
     *
     * @Companyid nvarchar(100),            ----公司FID
     * @funcode nvarchar(10),            -----职能代码
     * @FirstSerial nvarchar(50),            ----一级代码
     * @SecondSerial nvarchar(50),            ----二级代码
     * @Period nvarchar(100),            ----周期
     * @Time nvarchar(100),            ----时点
     * @Item nvarchar(500),            ----事项
     * @MaindeptCode nvarchar(100),            ----责任部门代码
     * @PersonA nvarchar(100),            ----责任人A代码
     * @PersonB nvarchar(100),            ----责任人B代码
     * @RelevantArchiveFID nvarchar(100),    ----相关档案FID    Relevantarchives_view 视图中FID字段
     * @WorkDesc nvarchar(100),            ----工作说明
     * @auditor nvarchar(100),            ----审批人
     * @FID nvarchar(100)    output	----返回值 EAS记录FID
     */
    private String callProcess(String bizId) {

//        if (StringUtils.isEmpty(bizId)) {
//            return bizId;
//        }
        //编写sql
        String tableName = getBizObjectFacade().getTableName(CustomSchemaCode.NORMAL_WORK_LIST_BASE);
        StringBuilder sql = new StringBuilder("SELECT * from ")
                .append(tableName).append(" where id ='")
                .append(bizId).append("';");
        //查询到入参
        Map<String, Object> map = sqlService.getMap(sql.toString());

        log.info("入参map={}", map);

        //调用存储过程
        String company = MapUtils.getString(map, "company", "");//公司FID
        String funcode = MapUtils.getString(map, "functionFNumber", "");//公司FID
        String firstCode = MapUtils.getString(map, "firstCode", "");//一级序号代码
        String secondCode = MapUtils.getString(map, "secondCode", "");//二级序号代码
        String cycle = MapUtils.getString(map, "cycle", "");//周期
        String pointOfTime = MapUtils.getString(map, "pointOfTime", "");//时点
        String event = MapUtils.getString(map, "event", "");//事项
        String responsibleDeptFNumber = MapUtils.getString(map, "responsibleDeptFNumber", "");//责任部门代码隐藏
        String personNumberA = MapUtils.getString(map, "personNumberA", "");//责任人A代码隐藏
        String personNumberB = MapUtils.getString(map, "personNumberB", "");//责任人B代码隐藏
        String relatedFileFID = MapUtils.getString(map, "relatedFileFID", "");//相关档案FID
        String workDesc = MapUtils.getString(map, "workDesc", "");//工作说明
        String auditer = MapUtils.getString(map, "auditer", "");//审批人
        String fid = MapUtils.getString(map, "fid", "");//fid


//        Assert.isFalse(StringUtils.isEmpty(company), "{}不能为空", "company");
//        Assert.isFalse(StringUtils.isEmpty(firstCode), "{}不能为空", "firstCode");
//        Assert.isFalse(StringUtils.isEmpty(secondCode), "{}不能为空", "secondCode");
//        Assert.isFalse(StringUtils.isEmpty(cycle), "{}不能为空", "cycle");
//        Assert.isFalse(StringUtils.isEmpty(pointOfTime), "{}不能为空", "pointOfTime");
//        Assert.isFalse(StringUtils.isEmpty(event), "{}不能为空", "event");
//        Assert.isFalse(StringUtils.isEmpty(responsibleDeptFNumber), "{}不能为空", "responsibleDeptFNumber");
//        Assert.isFalse(StringUtils.isEmpty(personNumberA), "{}不能为空", "personNumberA");
//        Assert.isFalse(StringUtils.isEmpty(personNumberB), "{}不能为空", "personNumberB");
//        Assert.isFalse(StringUtils.isEmpty(relatedFileFID), "{}不能为空", "relatedFileFID");

//        String execSql = String.format("exec [HG_LINK].[hg].[dbo].SyncNormalWork '%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s'",
//                company, funcode,firstCode, secondCode, cycle, pointOfTime, event, responsibleDeptFNumber, personNumberA, personNumberB, relatedFileFID, workDesc, auditer);
//
//        log.info("\n==========准备调用存储过程:{}", execSql);

//        sqlService.execute(CloudSqlService.htEas, execSql);

        JdbcTemplate jdbcTemplate = sqlService.getJdbcTemplateByDbCode(CloudSqlService.htEas);

        String returnValue = (String) jdbcTemplate.execute(
                new CallableStatementCreator() {
                    public CallableStatement createCallableStatement(Connection con) throws SQLException {
                        CallableStatement cs = con.prepareCall("{call [HG_LINK].[hg].[dbo].SyncNormalWork (?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
                        // 设置输入参数的值
                        cs.setString(1, company);
                        cs.setString(2, funcode);
                        cs.setString(3, firstCode);
                        cs.setString(4, secondCode);
                        cs.setString(5, cycle);
                        cs.setString(6, pointOfTime);
                        cs.setString(7, event);
                        cs.setString(8, responsibleDeptFNumber);
                        cs.setString(9, personNumberA);
                        cs.setString(10, personNumberB);
                        cs.setString(11, relatedFileFID);
                        cs.setString(12, workDesc);
                        cs.setString(13, auditer);
                        cs.setString(14, fid);
                        cs.registerOutParameter(14, Types.VARCHAR);// 注册输出参数的类型
                        return cs;
                    }
                }, new CallableStatementCallback() {
                    public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                        cs.execute();
                        String fid = cs.getString(14);
                        return fid;// 获取输出参数的值
                    }
                });

        log.info("\n=============存储过程执行完成，返回的fid:{}", returnValue);
        return returnValue;

    }

    /**
     * -----常态工作清单  作废
     * exec   NormalWorkDisable
     *
     * @param bizId
     * @Companyid nvarchar(100),            ----公司FID
     * @FirstSerial nvarchar(50),            ----一级代码   01 计划
     * @SecondSerial nvarchar(50),            ----二级代码   03 标准化
     * B@Item			nvarchar(500),			----事项
     * @auditor nvarchar(100)            ----审批人
     */
    private void callProcessToVoid(String bizId) {

        if (StringUtils.isEmpty(bizId)) {
            return;
        }
        //编写sql
        String tableName = getBizObjectFacade().getTableName(CustomSchemaCode.NORMAL_WORK_LIST_BASE);
        StringBuilder sql = new StringBuilder("SELECT * from ")
                .append(tableName).append(" where id ='")
                .append(bizId).append("';");
        //查询到入参
        Map<String, Object> map = sqlService.getMap(sql.toString());

        log.info("入参map={}", map);

        //调用存储过程
        String fid = MapUtils.getString(map, "fid", "");//fid

        JdbcTemplate jdbcTemplate = sqlService.getJdbcTemplateByDbCode(CloudSqlService.htEas);

        jdbcTemplate.execute(
                new CallableStatementCreator() {
                    public CallableStatement createCallableStatement(Connection con) throws SQLException {
                        CallableStatement cs = con.prepareCall("{call [HG_LINK].[hg].[dbo].NormalWorkDisable (?)}");
                        // 设置输入参数的值
                        cs.setString(1, fid);
                        return cs;
                    }
                }, new CallableStatementCallback() {
                    public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                        cs.execute();
                        return "success";// 获取输出参数的值
                    }
                });

        log.info("\n=============作废存储过程执行完成");

    }

//    /**
//     * -----常态工作清单  作废
//     * exec   NormalWorkDisable
//     *
//     * @param bizId
//     * @Companyid nvarchar(100),            ----公司FID
//     * @FirstSerial nvarchar(50),            ----一级代码   01 计划
//     * @SecondSerial nvarchar(50),            ----二级代码   03 标准化
//     * B@Item			nvarchar(500),			----事项
//     * @auditor nvarchar(100)            ----审批人
//     */
//    private void callProcessToVoid(String bizId) {
//
//        if (StringUtils.isEmpty(bizId)) {
//            return;
//        }
//        //编写sql
//        String tableName = getBizObjectFacade().getTableName(CustomSchemaCode.NORMAL_WORK_LIST_BASE);
//        StringBuilder sql = new StringBuilder("SELECT * from ")
//                .append(tableName).append(" where id ='")
//                .append(bizId).append("';");
//        //查询到入参
//        Map<String, Object> map = sqlService.getMap(sql.toString());
//
//        log.info("入参map={}", map);
//
//        //调用存储过程
//        String company = MapUtils.getString(map, "company", "");//公司FID
//        String firstCode = MapUtils.getString(map, "firstCode", "");//一级序号代码
//        String secondCode = MapUtils.getString(map, "secondCode", "");//二级序号代码
//        String event = MapUtils.getString(map, "event", "");//事项
//        String auditer = MapUtils.getString(map, "auditer", "");//审批人
//
//        Assert.isFalse(StringUtils.isEmpty(company), "%s不能为空", "company");
//        Assert.isFalse(StringUtils.isEmpty(firstCode), "%s不能为空", "firstCode");
//        Assert.isFalse(StringUtils.isEmpty(secondCode), "%s不能为空", "secondCode");
//        Assert.isFalse(StringUtils.isEmpty(auditer), "%s不能为空", "auditer");
//
//
//        String execSql = String.format("exec [HG_LINK].[hg].[dbo].NormalWorkDisable '%s','%s','%s','%s','%s'",
//                company, firstCode, secondCode, event, auditer);
//
//        log.info("\n==========准备调用作废存储过程:{}", execSql);
//
//        sqlService.execute(CloudSqlService.htEas, execSql);
//
//        log.info("\n=============作废存储过程执行完成");
//
//    }

    /**
     * 查询审批人,返回  员工号+姓名
     *
     * @param bizObject
     * @return
     */
    private String getFileBaseInfoWorkFlowApproval(BizObjectCreatedModel bizObject) {


        WorkflowInstanceModel instanceModel = getWorkflowInstanceFacade().getByObjectId(bizObject.getId());
        List<WorkItemModel> workItems = getWorkflowInstanceFacade().getWorkItems(instanceModel.getId(), true);
        final String finalActivityCode = "Activity10";
        Optional<WorkItemModel> first = workItems.stream().filter(a -> a.getActivityCode().equals(finalActivityCode)).findFirst();
        String participant = null;
        if (first.isPresent()) {
            WorkItemModel workItemModel = first.get();
            participant = workItemModel.getParticipant();
        }

        if (participant == null) {
            participant = bizObject.getCreater().getId();
        }

        UserModel user = getOrganizationFacade().getUser(participant);


        return new StringBuilder(user.getEmployeeNo()).append(" ").append(user.getName()).toString();
    }


    /**
     * 判断是否已存在
     *
     * @return
     */
    private String existsBizObject01(Map data) {

        //
        String companyNumber = (String) data.get("companyNumber");//公司
        String functionFNumber = (String) data.get("functionFNumber");//职能
        String firstCode = (String) data.get("firstCode");//一级序号
        String secondCode = (String) data.get("secondCode");//二级序号
        String responsibleDeptFNumber = (String) data.get("responsibleDeptFNumber");//责任部门
        String event = (String) data.get("event");//事项

        String tableName = getBizObjectFacade().getTableName(CustomSchemaCode.NORMAL_WORK_LIST_BASE);

//        StringBuilder sql = new StringBuilder("select id  from ").append(tableName).append(" where id ='").append(eventObjectId).append("'");

        StringBuilder sql = new StringBuilder("select id  from ").append(tableName)
                .append(" where companyNumber='").append(companyNumber)
                .append("' and functionFNumber='").append(functionFNumber)
                .append("' and firstCode='").append(firstCode)
                .append("' and secondCode='").append(secondCode)
                .append("' and responsibleDeptFNumber='").append(responsibleDeptFNumber)
                .append("' and event='").append(event)
                .append("';");

        Map<String, Object> map = sqlService.getMap(sql.toString());

        return (String) map.get("id");
    }
    /**
     * 判断是否已存在
     *
     * @return
     */
    private String existsBizObject(Map data) {

        //事项基础表id
        String eventObjectId = (String) data.get("eventObjectId");

        String tableName = getBizObjectFacade().getTableName(CustomSchemaCode.NORMAL_WORK_LIST_BASE);

        StringBuilder sql = new StringBuilder("select id  from ").append(tableName).append(" where id ='").append(eventObjectId).append("'");

        Map<String, Object> map = sqlService.getMap(sql.toString());

        return (String) map.get("id");
    }

    /**
     * 转换成  基础表-常态工作清单案 的数据
     *
     * @param map
     * @param auditDate
     * @return
     */
    private Map<String, Object> fileInfoBaseMap(Map<String, Object> map, String auditDate, String auditer) {
        Map<String, Object> data = new HashMap<>();


        //公司
        data.put("company", map.get("company"));
        //公司代码隐藏
        data.put("companyNumber", map.get("companyNumber"));
        //周期
        data.put("cycle", map.get("cycle"));
        //事项
        data.put("event", map.get("event"));
        //一级序号代码
        data.put("firstCode", map.get("firstCode"));
        //一级职责
        data.put("firstFun", map.get("firstFun"));
        //一级序号
        data.put("firstNumber", map.get("firstNumber"));
        //二级序号代码
        data.put("secondCode", map.get("secondCode"));
        //二级职责
        data.put("secondFun", map.get("secondFun"));
        //二级序号
        data.put("secondNumber", map.get("secondNumber"));
        //职能
        data.put("function", map.get("function"));
        //职能代码隐藏
        data.put("functionFNumber", map.get("functionFNumber"));
        //责任人A
        data.put("personNameA", map.get("personNameA"));
        //责任人B
        data.put("personNameB", map.get("personNameB"));
        //责任人A代码隐藏
        data.put("personNumberA", map.get("personNumberA"));
        //责任人B代码隐藏
        data.put("personNumberB", map.get("personNumberB"));
        //时点
        data.put("pointOfTime", map.get("pointOfTime"));
        //相关档案类型
        data.put("relatedFileYype", map.get("relatedFileYype"));
        //相关档案代码-FID
        data.put("relatedFileFID", map.get("relatedFileFID"));
        //相关档案代码隐藏
        data.put("relatedFileCode", map.get("relatedFileCode"));
        //相关档案
        data.put("relevantFileName", map.get("relevantFileName"));
        //相关档案备注
        data.put("relevantFileRemarks", map.get("relevantFileRemarks"));
        //责任人A代码
        data.put("relevPersonInfoBaseA", map.get("relevPersonInfoBaseA"));
        //责任人B代码
        data.put("relevPersonInfoBaseB", map.get("relevPersonInfoBaseB"));
        //责任部门代码
        data.put("responsibleDeptFid", map.get("responsibleDeptFid"));
        //责任部门代码隐藏
        data.put("responsibleDeptFNumber", map.get("responsibleDeptFNumber"));
        //责任部门
        data.put("responsibleDeptName", map.get("responsibleDeptName"));
        //工作说明
        data.put("workDesc", map.get("workDesc"));

        //审批时间
        data.put("auditDate", auditDate);
        //审批人
        data.put("auditer", auditer);

        return data;
    }
}
